﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;

namespace UpdateData.Common
{
    public class WriterChangeCell
    {
        /// <summary>
        /// 将字符串数组写入EXCEL
        /// </summary>
        /// <param name="xlFile">EXCEL文件</param>
        /// <param name="sheetName">EXCEL表sheet名称</param>
        /// <param name="changeDateEntities">输入修改位置和修改内容</param>
        /// <returns>EXCEL文件是否成功</returns>
        public void WriteExcel(string xlFile, string sheetName, List<ChangeDateEntity> changeDateEntities)
        {
            try
            {
                var extension = Path.GetExtension(xlFile).ToLower();
                IWorkbook wk = null;

                using (FileStream fs = File.Open(xlFile, FileMode.OpenOrCreate, FileAccess.Read, FileShare.ReadWrite))
                {
                    if (extension == ".xls")
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    else if (extension == ".xlsx")
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                }

                ISheet sheet = wk.GetSheet(sheetName);

                foreach (var item in changeDateEntities)
                {
                    if (string.IsNullOrEmpty(item.DataValue))
                        continue;
                    //获取行
                    IRow row = sheet.GetRow(item.RowIndex);
                    //在列单元格  
                    ICell cell = row.GetCell(item.ColumnIndex);

                    int value = 0;
                    double dvalue = 0f;
                    if (int.TryParse(item.DataValue, out value))
                    {
                        cell.SetCellValue(value);
                    }
                    else if (Double.TryParse(item.DataValue, out dvalue))
                    {
                        cell.SetCellValue(dvalue);
                    }
                    else
                    {
                        cell.SetCellValue(item.DataValue);
                    }

                }

                MemoryStream stream = new MemoryStream();
                wk.Write(stream);
                var buf = stream.ToArray();

                //保存为Excel文件  
                using (FileStream fs = new FileStream(xlFile, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }

                wk.Close();

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public List<string> ReaderExcel(string xlFile, List<TableLocation> changeDateEntities)
        {
            List<string> infoList = new List<string>();
            try
            {
                var extension = Path.GetExtension(xlFile).ToLower();
                IWorkbook wk = null;
                IFormulaEvaluator eva = null;

                using (FileStream fs = File.Open(xlFile, FileMode.OpenOrCreate, FileAccess.Read, FileShare.ReadWrite))
                {
                    if (extension == ".xls")
                    {
                        wk = new HSSFWorkbook(fs);
                        eva = new HSSFFormulaEvaluator(wk);
                    }
                    else if (extension == ".xlsx")
                    {
                        wk = new XSSFWorkbook(fs);
                        eva = new XSSFFormulaEvaluator(wk);
                    }
                }

                ISheet sheet = wk.GetSheetAt(0);

                int indexitem = 0;
                foreach (var item in changeDateEntities)
                {
                    //if (indexitem == 23)
                    //{
                    //}
                    //获取行
                    IRow row = sheet.GetRow(item.RowIndex);
                    //在列单元格  
                    ICell cell = row.GetCell(item.ColumnIndex);

                    string unit = "";
                    if (cell.CellType == CellType.Numeric)
                    {
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            unit = cell.DateCellValue.ToString("d");
                        }
                        else
                        {
                            unit = cell.ToString();
                        }
                    }
                    else if (cell.CellType == CellType.Blank)//空数据类型
                    {
                        unit = "";
                    }
                    else if (cell.CellType == CellType.Formula)//公式类型
                    {
                        //IFormulaEvaluator eva = null;
                        //if (extension == ".xls")
                        //{
                        //    eva = new HSSFFormulaEvaluator(wk);
                        //}
                        //else if (extension == ".xlsx")
                        //{
                        //    eva = new XSSFFormulaEvaluator(wk);
                        //}
                        unit = eva.Evaluate(cell).StringValue;
                        if (unit == null)
                        {
                            if (eva.Evaluate(cell).NumberValue > 0)
                                unit = eva.Evaluate(cell).NumberValue.ToString();
                            //unit = (eva.Evaluate(cell).NumberValue * 100).ToString("F1") + "%";
                            else
                                unit = "";
                        }

                    }
                    else //其他类型都按字符串类型来处理
                    {
                        unit = cell.StringCellValue;
                    }

                    infoList.Add(unit);
                    indexitem++;

                }
                wk.Close();
                return infoList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


    }
}
